Combine text and numbers

If a column you want to sort contains both numbers and text (such as Product #15, Product #100, Product #200), it may not sort as expected. You can use a number format to add text without changing the sorting behavior of the number.

Select the cells you want to format.
On the Format menu, click Cells, and then click the Number tab.
In the Category list, click a category, and then click a built-in format that resembles the one you want.
In the Category list, click Custom.

In the Type box, edit the number format codes to create the format you want.

To display both text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede the numbers with a backslash (\). Editing a built-in format does not remove the format.

To display	Use this code
12 as Product #12	"Product # " 0
12:00 as 12:00 AM EST	h:mm AM/PM "EST"
-12 as $-12.00 Shortage and 12 as $12.00 Surplus	$0.00 "Surplus";$-0.00 "Shortage"

Use the CONCATENATION, TEXT functions and the ampersand (&) operator to do this task.

Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.

Show How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3

A	B
Salesperson	Sales
Buchanan	28
Dodsworth	40%
Formula	Description (Result)
=A2&" sold "&B2&" units."	Combines contents above into a phrase (Buchanan sold 28 units)
=A3&" sold "&TEXT(B3,"0%")&" of the total sales."	Combines contents above into a phrase (Dodsworth sold 40% of the total sales)
=CONCATENATE(A2," sold ",B2," units.")	Combines contents above into a phrase (Buchanan sold 28 units)

Note the use of the TEXT function in the formula. When you join a number to a string of text by using the concatenation operator, use the TEXT function to format the number. The formula uses the underlying value from the referenced cell (.4 in this example)— not the formatted value you see in the cell (40%). The TEXT function restores the number formatting.

Functions details

CONCATENATE

TEXT